package com.wuliupai.delivery.wuliupaidelivery.db;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.wuliupai.delivery.wuliupaidelivery.utils.StringUtils;

import org.androidannotations.annotations.EBean;

import java.util.ArrayList;
import java.util.List;

@EBean(scope = EBean.Scope.Singleton)
public class DBUtil {

    /**
     * 获取所有的省份信息
     *
     * @param db
     * @return
     */
    public List<City> findAllProvince(SQLiteDatabase db) {
        String sql = "select code, provinceName, firstLetter from Province order by firstLetter";
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String code = cursor.getString(cursor.getColumnIndex("code"));
                String provinceName = cursor.getString(cursor.getColumnIndex("provinceName"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                City province = new City("", code, provinceName, firstLetter);
                list.add(province);
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }


    /**
     * 根据provinceCode找出Province
     *
     * @param db
     * @param provinceCode
     * @return
     */
    public static List<City> findProvinceByProvinceCode(SQLiteDatabase db, String provinceCode) {
        String sql = "select code, provinceName, firstLetter from Province where code = " + provinceCode;
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
//            String code = cursor.getString(cursor.getColumnIndex("code"));
                String cityName = cursor.getString(cursor.getColumnIndex("provinceName"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City("", provinceCode, cityName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }

    /**
     * 根据provinceName找出Province
     *
     * @param db
     * @param provinceName
     * @return
     */
    public List<City> findProvinceByProvinceName(SQLiteDatabase db, String provinceName) {
        String sql = "select code, provinceName, firstLetter from Province where provinceName like \"" + provinceName + "\"";
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String code = cursor.getString(cursor.getColumnIndex("code"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City("", code, provinceName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }

    /**
     * 根据省份code获取所有的城市信息
     *
     * @param db
     * @return
     */
    public List<City> findAllCity(SQLiteDatabase db, String provinceCode) {
        String sql = "select code, name, firstLetter from City where provinceCode = " + provinceCode + " order by firstLetter";
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String code = cursor.getString(cursor.getColumnIndex("code"));
                String cityName = cursor.getString(cursor.getColumnIndex("name"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City(provinceCode, code, cityName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }

    /**
     * 根据cityCode找出City
     *
     * @param db
     * @param cityCode
     * @return
     */
    public List<City> findCityByCityCode(SQLiteDatabase db, String cityCode) {
        String sql = "select provinceCode, name, firstLetter from City where code = " + cityCode;
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String provinceCode = cursor.getString(cursor.getColumnIndex("provinceCode"));
                String cityName = cursor.getString(cursor.getColumnIndex("name"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City(provinceCode, cityCode, cityName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }


    /**
     * 根据cityName找出City
     *
     * @param db
     * @param cityName
     * @return
     */
    public List<City> findCityByCityName(SQLiteDatabase db, String cityName) {
        String sql = "select provinceCode, code, firstLetter from City where name like \"" + cityName + "\"";
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String provinceCode = cursor.getString(cursor.getColumnIndex("provinceCode"));
                String cityCode = cursor.getString(cursor.getColumnIndex("code"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City(provinceCode, cityCode, cityName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }


    /**
     * 获取所有的区县信息
     *
     * @param db
     * @return
     */
    public List<City> findAllArea(SQLiteDatabase db, String cityCode) {
        String sql = "select code, areaName, firstLetter from Area where cityCode =" + cityCode + " order by firstLetter";
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String code = cursor.getString(cursor.getColumnIndex("code"));
                String areaName = cursor.getString(cursor.getColumnIndex("areaName"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City(cityCode, code, areaName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }

    /**
     * 根据areaCode找出Area
     *
     * @param db
     * @param areaCode
     * @return
     */
    public List<City> findAreaByAreaCode(SQLiteDatabase db, String areaCode) {
        String sql = "select cityCode, areaName, firstLetter from Area where code = " + areaCode;
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String cityCode = cursor.getString(cursor.getColumnIndex("cityCode"));
                String areaName = cursor.getString(cursor.getColumnIndex("areaName"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City(cityCode, areaCode, areaName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }


    /**
     * 根据areaName找出Area
     *
     * @param db
     * @param areaName
     * @return
     */
    public List<City> findAreaByAreaName(SQLiteDatabase db, String areaName) {
        String sql = "select code, cityCode, firstLetter from Area where areaName like \"" + areaName + "\"";
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String code = cursor.getString(cursor.getColumnIndex("code"));
                String cityCode = cursor.getString(cursor.getColumnIndex("cityCode"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City(cityCode, code, areaName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }

    /**
     * 模糊查询城市
     *
     * @param db
     * @param likeWord
     * @return
     */
    public List<City> queryCity(SQLiteDatabase db, String likeWord) {
        String sql = "select provinceCode, code, name, firstLetter from City where name like \"%" + likeWord + "%\" order by firstLetter";
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String provinceCode = cursor.getString(cursor.getColumnIndex("provinceCode"));
                String code = cursor.getString(cursor.getColumnIndex("code"));
                String cityName = cursor.getString(cursor.getColumnIndex("name"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City(provinceCode, code, cityName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }

    /**
     * 模糊查询区县
     *
     * @param db
     * @param likeWord
     * @return
     */
    public List<City> queryArea(SQLiteDatabase db, String likeWord) {
        String sql = "select cityCode, code, areaName, firstLetter from Area where areaName like \"%" + likeWord + "%\" order by firstLetter";
        List<City> list = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            if (cursor.getCount() == 0) {
                list.add(returnEmptyCity());
            }
            while (cursor.moveToNext()) {
                String cityCode = cursor.getString(cursor.getColumnIndex("cityCode"));
                String code = cursor.getString(cursor.getColumnIndex("code"));
                String areaName = cursor.getString(cursor.getColumnIndex("areaName"));
                String firstLetter = cursor.getString(cursor.getColumnIndex("firstLetter"));
                list.add(new City(cityCode, code, areaName, firstLetter));
            }
        } catch (Exception e) {
            e.printStackTrace();
            list.add(returnEmptyCity());
        }
        if (null != cursor) {
            cursor.close();
        }
        if (null != db) {
            db.close();
        }
        return list;
    }

    /**
     * 当数据库出现错误时,返回默认的空数据
     *
     * @return
     */
    private static City returnEmptyCity() {
        return new City("0", "0", "", "");
    }

    /**
     * 根据用户输入的关键字匹配相关的城市和区县
     *
     * @param db
     * @param key
     * @return
     */
    public List<CityHistoryBean> queryFitResult(SQLiteDatabase db, String key) {

        String sql = "SELECT cc.`name` AS city_name, cc.`code` AS city_code, aa.areaName AS area_name, aa.`code` AS area_code, pp.provinceName AS province_name, pp.`code` AS province_code FROM Area aa LEFT JOIN City cc ON aa.cityCode = cc.`code` LEFT JOIN Province pp ON cc.provinceCode = pp.`code` WHERE aa.areaName LIKE \"" + key + "%\" " +
                "UNION ALL " +
                "SELECT cc.`name` AS city_name, cc.`code` AS city_code, aa.areaName AS area_name, aa.`code` AS area_code, pp.provinceName AS province_name, pp.`code` AS province_code FROM City cc LEFT JOIN Area aa ON cc.`code` = aa.cityCode LEFT JOIN Province pp ON cc.provinceCode = pp.`code` WHERE cc.`name` LIKE \"" + key + "%\";";
        List<CityHistoryBean> list = new ArrayList<>();
        if (StringUtils.isEmpty(key)) {
            return list;
        }
        Cursor cursor = null;
        try {
            cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                String provinceCode = cursor.getString(cursor.getColumnIndex("province_code"));
                String province = cursor.getString(cursor.getColumnIndex("province_name"));
                String cityCode = cursor.getString(cursor.getColumnIndex("city_code"));
                String city = cursor.getString(cursor.getColumnIndex("city_name"));
                String areaCode = cursor.getString(cursor.getColumnIndex("area_code"));
                String area = cursor.getString(cursor.getColumnIndex("area_name"));
                list.add(new CityHistoryBean(areaCode, cityCode, provinceCode, area, city, province));
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        if (null != cursor) {
            cursor.close();
        }
        return list;
    }
}